<?php 
require_once ("DB.php");

/**
 * Fetch one project
 * @param string $id_project
 * @return 
 */
function service_projects_get_project_by_shorttitle($short_title) {
    $conn = getDBConnection();
    $sql_stmt = "SELECT pr.id_project, pr.title, pr.short_title, pr.start_time, pr.logo, pe.first_name, pe.last_name
		FROM projects pr, people pe
		WHERE pr.owner = pe.id_person AND short_title = '" . $short_title ."'
		LIMIT 1";
		
	$result = & $conn->query($sql_stmt);
	
	$project = array();
	while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $project['basic_info'] = $row;
		$project['optional_info'] = array();
    }

    $result->free();
	
	/* Fetch optional information*/
	
	$id_project = $project['basic_info']['id_project'];
	$sql_stmt = "SELECT profile_key, profile_value
		FROM projects_profiles
		WHERE id_project = " . $id_project;
	
	$result = & $conn->query($sql_stmt);
	
	while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
		$project['optional_info'][$row['profile_key']] = $row['profile_value'];
	}
	
	$result -> free();
	
    $conn->disconnect();
	
    return $project;
}

/**
 * Fetch the content of all project
 * including session id, title, starttime, duration, location, speaker id
 * @return array
 */
function service_projects_get_projectlist ($start = 0, $end = 50) {
	$conn = getDBConnection();
	
	/** Fecht overview information*/
	$sql_stmt = "SELECT pr.id_project, pr.title, pr.short_title, pr.start_time, pr.logo, pe.first_name, pe.last_name
		FROM projects pr, people pe
		WHERE pr.owner = pe.id_person
		ORDER BY pr.title ASC
		LIMIT ?, ?";
	
	$result = & $conn->query($sql_stmt, array($start, $end));
	
	$set = array();
	$project = array();
	while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $project['basic_info'] = $row;
		$project['optional_info'] = array();
		$set[] = $project;
    }

    $result->free();
	
	/* Fetch optional information*/
	foreach($set as $project) {
		$id_project = $project['basic_info']['id_project'];
		$sql_stmt = "SELECT profile_key, profile_value
			FROM projects_profiles
			WHERE id_project = " . $id_project;
		
		$result = & $conn->query($sql_stmt);
		
		while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
			$project['optional_info'][$row['profile_key']] = $row['profile_value'];
		}
		
		$result -> free();
	}
    $conn->disconnect();
	
    return $set;

}

?>